*------------------------------------------------------------------------------*
								***DATA CLEANING***
*------------------------------------------------------------------------------*

*** VOTING DATASET

**BW
local election_dates "16-10-1994 27-09-1998 22-9-2002 18-09-2005 27-09-2009 22-09-2013 24-09-2017"

foreach e of local election_dates {

	import excel "${rawdata}voting/Bundestagswahlen_Kreis-level/Bundestagswahl_Kreis-level_`e'.xlsx", sheet("14111-01-03-4") firstrow clear
	
	*Rename variables
	rename Kreise_code kreis_code
	rename KreiseundkreisfreieStädte Kreis
	rename Wahlberechtigte Eligible_voters
	rename Wahlbeteiligung turnout
	rename GültigeZweitstimmen Valid
	rename CDUCSU CDU
	rename GRÜNE GRUNE
	
	*Drop first and last observations
	drop if Kreis == ""
	drop if kreis_code == "DG"
	
	*Replace code for Hamburg and Berlin
	replace kreis_code = "02000" if Kreis == "  Hamburg"
	replace kreis_code = "11000" if Kreis == "  Berlin"
	drop if length(kreis_code)<5
	
	*Destring variables
	if "`e'" == "27-09-2009" {
		replace turnout = subinstr(turnout, ",", ".",.)
	} // "`e'" == "27-09-2009"
	foreach var of varlist Eligible_voters-SonstigeParteien{
		destring `var', replace force
	} // var
	replace turnout = turnout / 100
	
	*Generate Total votes
	gen Total_votes = round(Eligible_voters * turnout)
	
	*Construct reformed couties
	//Gottingen after addition of Osterode am Harz
	if "`e'" == "16-10-1994" | "`e'" == "27-09-1998" | "`e'" == "22-9-2002" | "`e'" == "18-09-2005" | "`e'" == "27-09-2009" | "`e'" == "22-09-2013" {
		foreach var of varlist Eligible_voters Total_votes Valid CDU SPD GRUNE FDP DIELINKE AfD SonstigeParteien {
			sum `var' if kreis_code == "03152"  // Gottingen, Landkeis
			scalar `var'_3152 = r(mean)
			sum `var' if kreis_code == "03156"  // Osterode an Harz, Landkreis
			scalar `var'_3156 = r(mean)
			replace `var' = `var'_3152 + `var'_3156 if kreis_code == "03159"
		} // var
		replace turnout = Total_votes / Eligible_voters if kreis_code == "03159"
	} // "`e'" == "16-10-1994" | "`e'" == "27-09-1998" | "`e'" == "22-9-2002" | "`e'" == "18-09-2005" | "`e'" == "27-09-2009"

	//Städteregion Aachen (einschl. Stadt Aachen)
	if "`e'" == "16-10-1994" | "`e'" == "27-09-1998" | "`e'" == "22-9-2002" | "`e'" == "18-09-2005" | "`e'" == "27-09-2009" {
		foreach var of varlist Eligible_voters Total_votes Valid CDU SPD GRUNE FDP DIELINKE AfD SonstigeParteien {
			sum `var' if kreis_code == "05354"  // Aachen, Kreis
			scalar `var'_5354 = r(mean)
			sum `var' if kreis_code == "05334002"  // Aachen, krfr. Stadt
			scalar `var'_5334002 = r(mean)
			replace `var' = `var'_5354 + `var'_5334002 if kreis_code == "05334"
		} // var
		replace turnout = Total_votes / Eligible_voters if kreis_code == "05334"
	} // "`e'" == "16-10-1994" | "`e'" == "27-09-1998" | "`e'" == "22-9-2002" | "`e'" == "18-09-2005" | "`e'" == "27-09-2009"
	
	drop if Eligible_voters == . & turnout == . & Valid == . & CDU == . & SPD == . & GRUNE == . & FDP == . & DIELINKE == . & AfD == . & SonstigeParteien == .
	drop if kreis_code == "03152" | kreis_code == "03156"
	
	*Generate election date
	gen str election_date_str = "`e'"
	generate double election_date = date(election_date_str, "DMY")
	format election_date %td
	drop election_date_str
	
	*Generate election type
	gen str election_typeBWorLW = "BW"
	
	*Generare Landtagswahlen
	gen str Land = "Schleswig-Holstein" if substr(kreis_code, 1, 2) == "01"
	replace Land = "Hamburg" if substr(kreis_code, 1, 2) == "02"
	replace Land = "Niedersachsen" if substr(kreis_code, 1, 2) == "03"
	replace Land = "Bremen" if substr(kreis_code, 1, 2) == "04"
	replace Land = "Nordrhein-Westfalen" if substr(kreis_code, 1, 2) == "05"
	replace Land = "Hessen" if substr(kreis_code, 1, 2) == "06"
	replace Land = "Rheinland-Pfalz" if substr(kreis_code, 1, 2) == "07"
	replace Land = "Baden-Württemberg" if substr(kreis_code, 1, 2) == "08"
	replace Land = "Bayern" if substr(kreis_code, 1, 2) == "09"
	replace Land = "Saarland" if substr(kreis_code, 1, 2) == "10"
	replace Land = "Berlin" if substr(kreis_code, 1, 2) == "11"
	replace Land = "Brandenburg" if substr(kreis_code, 1, 2) == "12"
	replace Land = "Mecklenburg-Vorpommern" if substr(kreis_code, 1, 2) == "13"
	replace Land = "Sachsen" if substr(kreis_code, 1, 2) == "14"
	replace Land = "Sachsen-Anhalt" if substr(kreis_code, 1, 2) == "15"
	replace Land = "Thüringen" if substr(kreis_code, 1, 2) == "16"
	
	replace Kreis = substr(Kreis, 3, .) if Kreis == "  Hamburg" | Kreis == "  Berlin"
	replace Kreis = substr(Kreis, 7, .) if (Kreis != "Hamburg" & Kreis != "Berlin")	

	
	*Save
	order kreis_code Kreis election_date Land election_typeBWorLW Eligible_voters Valid turnout
	
	qui compress
	save "${tempdata}Bundestagswahl_Kreis_`e'.dta", replace

} // e

*Create uniqie Bundestagswahl file
use "${tempdata}Bundestagswahl_Kreis_16-10-1994.dta", clear

local election_dates_2 "27-09-1998 22-9-2002 18-09-2005 27-09-2009 22-09-2013 24-09-2017"

*Append
foreach e of local election_dates_2 {
	append using "${tempdata}Bundestagswahl_Kreis_`e'.dta"
}

*Save
qui compress
save "${tempdata}BW_Kreis.dta", replace

*Erase individual files
foreach e of local election_dates {
	erase "${tempdata}Bundestagswahl_Kreis_`e'.dta"
} // e

*------------------------------------------

**LW

local lander "Baden-Wu╠êrttemberg Bayern Brandenburg Bremen Hessen Mecklenburg-Vorpommern Niedersachsen Nordrhein-Westfalen Rheinland-Pfalz Saarland Sachsen Sachsen-Anhalt Schleswig-Holstein Thu╠êringen"
local lander_stadt "Berlin Hamburg"

foreach l of local lander {

	import excel "${rawdata}voting/Landtagswahlen - Kreis Ebene/`l'_Landtagswahl_Kreis-level.xlsx", sheet("Sheet 1") firstrow clear
	
	*Rename variables
	rename Kreise_code kreis_code
	rename Landtagswahlen Land
	rename KreiseundkreisfreieStädte Kreis
	rename Wahlberechtigte Eligible_voters
	rename Wahlbeteiligung turnout
	rename GültigeStimmen Valid
	rename CDUCSU CDU
	rename GRÜNE GRUNE

	*Drop first and last observations
	drop if Kreis == ""
	drop if length(kreis_code)<5

	*Destring variables
	foreach var of varlist Eligible_voters-SonstigeParteien{
		destring `var', replace force
	} // var
	replace turnout = turnout / 100
	
	*Generate Total votes
	gen Total_votes = round(Eligible_voters * turnout)
	
	*Generate election date
	generate double Election_date = date(election_date, "DMY")
	format Election_date %td
	drop election_date
	rename Election_date election_date
	
	*Generate election type
	gen str election_typeBWorLW = "LW"
	
	*Construct remormed couties
	//Gottingen after addition of Osterode am Harz
		foreach y in 1998 2003 2008 2013 {
		foreach var of varlist Eligible_voters Total_votes Valid CDU SPD GRUNE FDP DIELINKE AfD SonstigeParteien {
			sum `var' if (kreis_code == "03152" & year(election_date) == `y')  // Gottingen, Landkreis
			scalar `var'_3152_`y' = r(mean)
			sum `var' if (kreis_code == "03156" & year(election_date) == `y')  // Osterode am Harz, Landkreis
			scalar `var'_3156_`y' = r(mean)
			replace `var' = `var'_3152_`y' + `var'_3156_`y' if (kreis_code == "03159" & year(election_date) == `y')
		} // var
	} // y
	replace turnout = Total_votes / Eligible_voters if kreis_code == "03159"

	//Städteregion Aachen (einschl. Stadt Aachen)
	foreach y in 1995 2000 2005 {
		foreach var of varlist Eligible_voters Total_votes Valid CDU SPD GRUNE FDP DIELINKE AfD SonstigeParteien {
			sum `var' if (kreis_code == "05354" & year(election_date) == `y')  // Aachen, Kreis
			scalar `var'_5354_`y' = r(mean)
			sum `var' if (kreis_code == "05334002" & year(election_date) == `y')  // Aachen, krfr. Stadt
			scalar `var'_5334002_`y' = r(mean)
			replace `var' = `var'_5354_`y' + `var'_5334002_`y' if (kreis_code == "05334" & year(election_date) == `y')
		} // var
	} // y
	replace turnout = Total_votes / Eligible_voters if kreis_code == "05334"
	
	drop if Eligible_voters == . & turnout == . & Valid == . & CDU == . & SPD == . & GRUNE == . & FDP == . & DIELINKE == . & AfD == . & SonstigeParteien == .
	drop if kreis_code == "03152" | kreis_code == "03156"
	
	replace Kreis = substr(Kreis, 5, .)

	*Save
	order kreis_code Kreis election_date Land election_typeBWorLW

	qui compress
	save "${tempdata}/`l'_Landtagswahl_Kreis-level.dta", replace

} // l

foreach l of local lander_stadt {

	import excel "${rawdata}voting/Landtagswahlen - Kreis Ebene//`l'_Landtagswahl.xlsx", sheet("Sheet 1") firstrow clear
	
	*Rename variables
	rename Kreise_code kreis_code
	rename Landtagswahlen Land
	rename KreiseundkreisfreieStädte Kreis
	rename Wahlberechtigte Eligible_voters
	rename Wahlbeteiligung turnout
	rename GültigeStimmen Valid
	rename CDUCSU CDU
	rename GRÜNE GRUNE

	*Drop last observations
	drop if Kreis == ""
	
	*Replace kreise code
	replace kreis_code = "02000" if Kreis == "Hamburg"
	replace kreis_code = "11000" if Kreis == "Berlin"
	drop if length(kreis_code)<5

	*Destring variables
	foreach var of varlist Eligible_voters-SonstigeParteien{
		destring `var', replace force
	} // var
	replace turnout = turnout / 100
	drop if Eligible_voters == . & turnout == . & Valid == . & CDU == . & SPD == . & GRUNE == . & FDP == . & DIELINKE == . & AfD == . & SonstigeParteien == .

	*Generate election date
	generate double Election_date = date(election_date, "DMY")
	format Election_date %td
	drop election_date
	rename Election_date election_date

	*Generate election type
	gen str election_typeBWorLW = "LW"
	
	if "`l'" == "Berlin"{
		replace Kreis = substr(Kreis, 5, .) if Kreis != "Berlin"
	} // l == Berlin

	*Save
	order kreis_code Kreis election_date Land election_typeBWorLW

	qui compress
	save "${tempdata}/`l'_Landtagswahl_Kreis-level.dta", replace

} // l

*Create unique Landtagswahl file
use "${tempdata}Baden-Wu╠êrttemberg_Landtagswahl_Kreis-level.dta", clear

local lander_2 "Bayern Berlin Brandenburg Bremen Hamburg Hessen Mecklenburg-Vorpommern Niedersachsen Nordrhein-Westfalen Rheinland-Pfalz Saarland Sachsen Sachsen-Anhalt Schleswig-Holstein Thu╠êringen"

*Append
foreach l of local lander_2 {
	append using "${tempdata}/`l'_Landtagswahl_Kreis-level.dta"
} // l

*Save
qui compress
save "${tempdata}LW_Kreis.dta", replace

*Erase individual files
foreach l of local lander_2 {
	erase "${tempdata}/`l'_Landtagswahl_Kreis-level.dta"
} // L
erase "${tempdata}Baden-Wu╠êrttemberg_Landtagswahl_Kreis-level.dta"

*------------------------------------------

**Full voting dataset
use "${tempdata}BW_Kreis.dta", clear

append using "${tempdata}LW_Kreis.dta"

gen str Land_code = substr(kreis_code, 1, 2)
destring Land_code, replace

destring kreis_code, replace

*Merge file with new definition of counties in Mecklenburg-Vorpommern, Sachsen and Sachsen-Anhalt
merge 1:1 kreis_code election_date election_typeBWorLW using "${tempdata}MV_S_SA_reformed_counties_voting.dta", nogen

*Generate other parties including AfD
replace AfD = 0 if AfD == .  // In this way it doesn't alter the other's share
gen OtherParties = SonstigeParteien + AfD

replace DIELINKE = 0 if DIELINKE == .  // Think more about this

*Generate Statt-Partei
*total votes = vote share (from https://de.wikipedia.org/wiki/Wahlergebnisse_in_Hamburg#B%C3%BCrgerschaftswahlen_ab_1946) * Valid
gen STATTPARTEI = .
replace STATTPARTEI = 31271 if election_typeBWorLW == "LW" & election_date == td(21sep1997) & Land == "Hamburg"
replace STATTPARTEI = 3403 if election_typeBWorLW == "LW" & election_date == td(23sep2001) & Land == "Hamburg"
label variable STATTPARTEI "Statt-Partei"

*Generate Partei Rechtsstaatlicher Offensive
gen SCHILL = .
replace SCHILL = 165030 if election_typeBWorLW == "LW" & election_date == td(23sep2001) & Land == "Hamburg"
replace SCHILL = 28844 if election_typeBWorLW == "LW" & election_date == td(29feb2004) & Land == "Hamburg"
label variable SCHILL "Schill-Partei"

*Generate Incumbent
gen Incumbent = .
label variable Incumbent "Incumbent"

	//BW
	replace Incumbent = ( SPD + FDP ) if election_typeBWorLW == "BW" & election_date == td(05oct1980)
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "BW" & election_date == td(06mar1983)
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "BW" & election_date == td(25jan1987)
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "BW" & election_date == td(02dec1990)
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "BW" & election_date == td(16oct1994)
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "BW" & election_date == td(27sep1998)
	replace Incumbent = ( SPD + GRUNE ) if election_typeBWorLW == "BW" & election_date == td(22sep2002)
	replace Incumbent = ( SPD + GRUNE ) if election_typeBWorLW == "BW" & election_date == td(18sep2005)
	replace Incumbent = ( CDU + SPD ) if election_typeBWorLW == "BW" & election_date == td(27sep2009)
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "BW" & election_date == td(22sep2013)
	replace Incumbent = ( CDU + SPD ) if election_typeBWorLW == "BW" & election_date == td(24sep2017)
	
	//LW : Baden-Württemberg
	replace Incumbent = ( CDU ) if election_typeBWorLW == "LW" & election_date == td(16mar1980) & Land == "Baden-Württemberg"
	replace Incumbent = ( CDU ) if election_typeBWorLW == "LW" & election_date == td(25mar1984) & Land == "Baden-Württemberg"
	replace Incumbent = ( CDU ) if election_typeBWorLW == "LW" & election_date == td(20mar1988) & Land == "Baden-Württemberg"
	replace Incumbent = ( CDU ) if election_typeBWorLW == "LW" & election_date == td(05apr1992) & Land == "Baden-Württemberg"
	replace Incumbent = ( CDU + SPD ) if election_typeBWorLW == "LW" & election_date == td(24mar1996) & Land == "Baden-Württemberg"
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "LW" & election_date == td(25mar2001) & Land == "Baden-Württemberg"
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "LW" & election_date == td(26mar2006) & Land == "Baden-Württemberg"
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "LW" & election_date == td(27mar2011) & Land == "Baden-Württemberg"
	replace Incumbent = ( SPD + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(13mar2016) & Land == "Baden-Württemberg"

	//LW : Bayern
	replace Incumbent = ( CDU ) if election_typeBWorLW == "LW" & election_date == td(10oct1982) & Land == "Bayern"
	replace Incumbent = ( CDU ) if election_typeBWorLW == "LW" & election_date == td(12oct1986) & Land == "Bayern"
	replace Incumbent = ( CDU ) if election_typeBWorLW == "LW" & election_date == td(14oct1990) & Land == "Bayern"
	replace Incumbent = ( CDU ) if election_typeBWorLW == "LW" & election_date == td(05apr1992) & Land == "Bayern"
	replace Incumbent = ( CDU ) if election_typeBWorLW == "LW" & election_date == td(25sep1994) & Land == "Bayern"
	replace Incumbent = ( CDU ) if election_typeBWorLW == "LW" & election_date == td(13sep1998) & Land == "Bayern"
	replace Incumbent = ( CDU ) if election_typeBWorLW == "LW" & election_date == td(21sep2003) & Land == "Bayern"
	replace Incumbent = ( CDU ) if election_typeBWorLW == "LW" & election_date == td(28sep2008) & Land == "Bayern"
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "LW" & election_date == td(15sep2013) & Land == "Bayern"
	replace Incumbent = ( CDU ) if election_typeBWorLW == "LW" & election_date == td(14oct2018) & Land == "Bayern"

	//LW : Berlin
	replace Incumbent = ( CDU + SPD ) if election_typeBWorLW == "LW" & election_date == td(22oct1995) & Land == "Berlin"
	replace Incumbent = ( CDU + SPD ) if election_typeBWorLW == "LW" & election_date == td(10oct1999) & Land == "Berlin"
	replace Incumbent = ( CDU + SPD ) if election_typeBWorLW == "LW" & election_date == td(21oct2001) & Land == "Berlin"
	replace Incumbent = ( SPD + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(17sep2006) & Land == "Berlin"
	replace Incumbent = ( SPD + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(18sep2011) & Land == "Berlin"
	replace Incumbent = ( SPD + CDU ) if election_typeBWorLW == "LW" & election_date == td(18sep2016) & Land == "Berlin"
	
	//LW : Brandenburg
	replace Incumbent = ( SPD + FDP + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(11sep1994) & Land == "Brandenburg"
	replace Incumbent = ( SPD ) if election_typeBWorLW == "LW" & election_date == td(05sep1999) & Land == "Brandenburg"
	replace Incumbent = ( SPD + CDU ) if election_typeBWorLW == "LW" & election_date == td(19sep2004) & Land == "Brandenburg"
	replace Incumbent = ( SPD + CDU ) if election_typeBWorLW == "LW" & election_date == td(27sep2009) & Land == "Brandenburg"
	replace Incumbent = ( SPD + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(14sep2014) & Land == "Brandenburg"
	replace Incumbent = ( SPD + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(1sep2019) & Land == "Brandenburg"
	
	//LW : Bremen
	replace Incumbent = ( SPD ) if election_typeBWorLW == "LW" & election_date == td(25sep1983) & Land == "Bremen"
	replace Incumbent = ( SPD ) if election_typeBWorLW == "LW" & election_date == td(13sep1987) & Land == "Bremen"
	replace Incumbent = ( SPD ) if election_typeBWorLW == "LW" & election_date == td(29sep1991) & Land == "Bremen"
	replace Incumbent = ( SPD + FDP + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(14may1995) & Land == "Bremen"
	replace Incumbent = ( SPD + CDU ) if election_typeBWorLW == "LW" & election_date == td(06jun1999) & Land == "Bremen"
	replace Incumbent = ( SPD + CDU ) if election_typeBWorLW == "LW" & election_date == td(25may2003) & Land == "Bremen"
	replace Incumbent = ( SPD + CDU ) if election_typeBWorLW == "LW" & election_date == td(13may2007) & Land == "Bremen"
	replace Incumbent = ( SPD + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(22may2011) & Land == "Bremen"
	replace Incumbent = ( SPD + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(10may2015) & Land == "Bremen"
	replace Incumbent = ( SPD + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(26may2019) & Land == "Bremen"

	//LW : Hamburg
	replace Incumbent = ( SPD ) if election_typeBWorLW == "LW" & election_date == td(06jun1982) & Land == "Hamburg"
	replace Incumbent = ( SPD ) if election_typeBWorLW == "LW" & election_date == td(09nov1986) & Land == "Hamburg"
	replace Incumbent = ( SPD + FDP ) if election_typeBWorLW == "LW" & election_date == td(02may1991) & Land == "Hamburg"
	replace Incumbent = ( SPD ) if election_typeBWorLW == "LW" & election_date == td(19sep1993) & Land == "Hamburg"
	replace Incumbent = ( SPD + STATTPARTEI ) if election_typeBWorLW == "LW" & election_date == td(21sep1997) & Land == "Hamburg"
	replace Incumbent = ( SPD + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(23sep2001) & Land == "Hamburg"
	replace Incumbent = ( CDU + FDP + SCHILL ) if election_typeBWorLW == "LW" & election_date == td(29feb2004) & Land == "Hamburg"
	replace Incumbent = ( CDU ) if election_typeBWorLW == "LW" & election_date == td(27feb2008) & Land == "Hamburg"
	replace Incumbent = ( CDU + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(20feb2011) & Land == "Hamburg"
	replace Incumbent = ( SPD ) if election_typeBWorLW == "LW" & election_date == td(15feb2015) & Land == "Hamburg"

	//LW : Hessen
	replace Incumbent = ( SPD + FDP ) if election_typeBWorLW == "LW" & election_date == td(26sep1982) & Land == "Hessen"
	replace Incumbent = ( CDU ) if election_typeBWorLW == "LW" & election_date == td(20jan1991) & Land == "Hessen"
	replace Incumbent = ( SPD + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(07feb1999) & Land == "Hessen"
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "LW" & election_date == td(02feb2003) & Land == "Hessen"
	replace Incumbent = ( CDU ) if election_typeBWorLW == "LW" & election_date == td(27jan2008) & Land == "Hessen"
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "LW" & election_date == td(22sep2013) & Land == "Hessen"
	replace Incumbent = ( CDU + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(28oct2018) & Land == "Hessen"

	//LW : Mecklenburg-Vorpommern
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "LW" & election_date == td(16oct1994) & Land == "Mecklenburg-Vorpommern"
	replace Incumbent = ( CDU + SPD ) if election_typeBWorLW == "LW" & election_date == td(27sep1998) & Land == "Mecklenburg-Vorpommern"
	replace Incumbent = ( SPD + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(22sep2002) & Land == "Mecklenburg-Vorpommern"
	replace Incumbent = ( SPD + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(17sep2006) & Land == "Mecklenburg-Vorpommern"
	replace Incumbent = ( SPD + CDU ) if election_typeBWorLW == "LW" & election_date == td(04sep2011) & Land == "Mecklenburg-Vorpommern"
	replace Incumbent = ( SPD + CDU ) if election_typeBWorLW == "LW" & election_date == td(04sep2016) & Land == "Mecklenburg-Vorpommern"
	
	//LW : Niedersachsen
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "LW" & election_date == td(13may1990) & Land == "Niedersachsen"
	replace Incumbent = ( SPD + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(13mar1994) & Land == "Niedersachsen"
	replace Incumbent = ( SPD ) if election_typeBWorLW == "LW" & election_date == td(01mar1998) & Land == "Niedersachsen"
	replace Incumbent = ( SPD ) if election_typeBWorLW == "LW" & election_date == td(02feb2003) & Land == "Niedersachsen"
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "LW" & election_date == td(27jan2008) & Land == "Niedersachsen"
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "LW" & election_date == td(20jan2013) & Land == "Niedersachsen"
	replace Incumbent = ( SPD + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(15oct2017) & Land == "Niedersachsen"
	
	//LW : Nordrhein-Westfalen
	replace Incumbent = ( SPD + FDP ) if election_typeBWorLW == "LW" & election_date == td(11may1980) & Land == "Nordrhein-Westfalen"
	replace Incumbent = ( SPD ) if election_typeBWorLW == "LW" & election_date == td(12may1985) & Land == "Nordrhein-Westfalen"
	replace Incumbent = ( SPD ) if election_typeBWorLW == "LW" & election_date == td(13may1990) & Land == "Nordrhein-Westfalen"
	replace Incumbent = ( SPD ) if election_typeBWorLW == "LW" & election_date == td(14may1995) & Land == "Nordrhein-Westfalen"
	replace Incumbent = ( SPD + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(14may2000) & Land == "Nordrhein-Westfalen"
	replace Incumbent = ( SPD + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(22may2005) & Land == "Nordrhein-Westfalen"
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "LW" & election_date == td(09may2010) & Land == "Nordrhein-Westfalen"
	replace Incumbent = ( SPD + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(13may2012) & Land == "Nordrhein-Westfalen"
	replace Incumbent = ( SPD + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(14may2017) & Land == "Nordrhein-Westfalen"

	//LW : Rheinland-Pfalz
	replace Incumbent = ( CDU ) if election_typeBWorLW == "LW" & election_date == td(06mar1983) & Land == "Rheinland-Pfalz"
	replace Incumbent = ( CDU ) if election_typeBWorLW == "LW" & election_date == td(17may1987) & Land == "Rheinland-Pfalz"
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "LW" & election_date == td(21apr1991) & Land == "Rheinland-Pfalz"
	replace Incumbent = ( SPD + FDP ) if election_typeBWorLW == "LW" & election_date == td(24mar1996) & Land == "Rheinland-Pfalz"
	replace Incumbent = ( SPD + FDP ) if election_typeBWorLW == "LW" & election_date == td(25mar2001) & Land == "Rheinland-Pfalz"
	replace Incumbent = ( SPD + FDP ) if election_typeBWorLW == "LW" & election_date == td(26mar2006) & Land == "Rheinland-Pfalz"
	replace Incumbent = ( SPD ) if election_typeBWorLW == "LW" & election_date == td(27mar2011) & Land == "Rheinland-Pfalz"
	replace Incumbent = ( SPD + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(13mar2016) & Land == "Rheinland-Pfalz"
	
	//LW : Saarland
	replace Incumbent = ( CDU + SPD ) if election_typeBWorLW == "LW" & election_date == td(27apr1980) & Land == "Saarland"
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "LW" & election_date == td(10mar1985) & Land == "Saarland"
	replace Incumbent = ( SPD ) if election_typeBWorLW == "LW" & election_date == td(28jan1990) & Land == "Saarland"
	replace Incumbent = ( SPD ) if election_typeBWorLW == "LW" & election_date == td(16oct1994) & Land == "Saarland"
	replace Incumbent = ( SPD ) if election_typeBWorLW == "LW" & election_date == td(05sep1999) & Land == "Saarland"
	replace Incumbent = ( CDU ) if election_typeBWorLW == "LW" & election_date == td(05sep2004) & Land == "Saarland"
	replace Incumbent = ( CDU ) if election_typeBWorLW == "LW" & election_date == td(30aug2009) & Land == "Saarland"
	replace Incumbent = ( CDU + FDP + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(25mar2012) & Land == "Saarland"
	replace Incumbent = ( CDU + SPD ) if election_typeBWorLW == "LW" & election_date == td(26mar2017) & Land == "Saarland"
	
	//LW : Sachsen
	replace Incumbent = ( CDU ) if election_typeBWorLW == "LW" & election_date == td(11sep1994) & Land == "Sachsen"
	replace Incumbent = ( CDU ) if election_typeBWorLW == "LW" & election_date == td(19sep1999) & Land == "Sachsen"
	replace Incumbent = ( CDU ) if election_typeBWorLW == "LW" & election_date == td(19sep2004) & Land == "Sachsen"
	replace Incumbent = ( CDU + SPD ) if election_typeBWorLW == "LW" & election_date == td(30aug2009) & Land == "Sachsen"
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "LW" & election_date == td(31aug2014) & Land == "Sachsen"
	
	//LW : Sachsen-Anhalt
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "LW" & election_date == td(26jun1994) & Land == "Sachsen-Anhalt"
	replace Incumbent = ( SPD + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(26apr1998) & Land == "Sachsen-Anhalt"
	replace Incumbent = ( SPD ) if election_typeBWorLW == "LW" & election_date == td(21apr2002) & Land == "Sachsen-Anhalt"
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "LW" & election_date == td(26mar2006) & Land == "Sachsen-Anhalt"
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "LW" & election_date == td(20mar2011) & Land == "Sachsen-Anhalt"
	replace Incumbent = ( CDU + SPD + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(13mar2016) & Land == "Sachsen-Anhalt"
	
	//LW : Schleswig-Holstein
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "LW" & election_date == td(08may1988) & Land == "Schleswig-Holstein"
	replace Incumbent = ( SPD ) if election_typeBWorLW == "LW" & election_date == td(05apr1992) & Land == "Schleswig-Holstein"
	replace Incumbent = ( SPD ) if election_typeBWorLW == "LW" & election_date == td(24mar1996) & Land == "Schleswig-Holstein"
	replace Incumbent = ( SPD + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(27feb2000) & Land == "Schleswig-Holstein"
	replace Incumbent = ( SPD + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(20feb2005) & Land == "Schleswig-Holstein"
	replace Incumbent = ( CDU + SPD ) if election_typeBWorLW == "LW" & election_date == td(27sep2009) & Land == "Schleswig-Holstein"
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "LW" & election_date == td(06may2012) & Land == "Schleswig-Holstein"
	replace Incumbent = ( SPD + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(07may2017) & Land == "Schleswig-Holstein"
	
	//LW : Thüringen
	replace Incumbent = ( CDU + FDP ) if election_typeBWorLW == "LW" & election_date == td(16oct1994) & Land == "Thüringen"
	replace Incumbent = ( CDU + SPD ) if election_typeBWorLW == "LW" & election_date == td(12sep1999) & Land == "Thüringen"
	replace Incumbent = ( CDU ) if election_typeBWorLW == "LW" & election_date == td(13jun2004) & Land == "Thüringen"
	replace Incumbent = ( CDU + SPD ) if election_typeBWorLW == "LW" & election_date == td(30aug2009) & Land == "Thüringen"
	replace Incumbent = ( CDU + SPD ) if election_typeBWorLW == "LW" & election_date == td(14sep2014) & Land == "Thüringen"
	replace Incumbent = ( SPD + DIELINKE + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(27oct2019) & Land == "Thüringen"

*Generate Established opposition
gen Est_opp = .
label variable Est_opp "Established opposition"

	//BW
	replace Est_opp = ( CDU + GRUNE + DIELINKE ) if election_typeBWorLW == "BW" & election_date == td(05oct1980)
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "BW" & election_date == td(06mar1983)
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "BW" & election_date == td(25jan1987)
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "BW" & election_date == td(02dec1990)
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "BW" & election_date == td(16oct1994)
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "BW" & election_date == td(27sep1998)
	replace Est_opp = ( CDU + FDP + DIELINKE ) if election_typeBWorLW == "BW" & election_date == td(22sep2002)
	replace Est_opp = ( CDU + FDP + DIELINKE ) if election_typeBWorLW == "BW" & election_date == td(18sep2005)
	replace Est_opp = ( FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "BW" & election_date == td(27sep2009)
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "BW" & election_date == td(22sep2013)
	replace Est_opp = ( FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "BW" & election_date == td(24sep2017)
	
	//LW : Baden-Württemberg
	replace Est_opp = ( SPD + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(16mar1980) & Land == "Baden-Württemberg"
	replace Est_opp = ( SPD + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(25mar1984) & Land == "Baden-Württemberg"
	replace Est_opp = ( SPD + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(20mar1988) & Land == "Baden-Württemberg"
	replace Est_opp = ( SPD + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(05apr1992) & Land == "Baden-Württemberg"
	replace Est_opp = ( FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(24mar1996) & Land == "Baden-Württemberg"
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(25mar2001) & Land == "Baden-Württemberg"
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(26mar2006) & Land == "Baden-Württemberg"
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(27mar2011) & Land == "Baden-Württemberg"
	replace Est_opp = ( CDU + FDP + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(13mar2016) & Land == "Baden-Württemberg"

	//LW : Bayern
	replace Est_opp = ( SPD + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(10oct1982) & Land == "Bayern"
	replace Est_opp = ( SPD + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(12oct1986) & Land == "Bayern"
	replace Est_opp = ( SPD + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(14oct1990) & Land == "Bayern"
	replace Est_opp = ( SPD + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(05apr1992) & Land == "Bayern"
	replace Est_opp = ( SPD + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(25sep1994) & Land == "Bayern"
	replace Est_opp = ( SPD + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(13sep1998) & Land == "Bayern"
	replace Est_opp = ( SPD + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(21sep2003) & Land == "Bayern"
	replace Est_opp = ( SPD + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(28sep2008) & Land == "Bayern"
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(15sep2013) & Land == "Bayern"
	replace Est_opp = ( SPD + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(14oct2018) & Land == "Bayern"

	//LW : Berlin
	replace Est_opp = ( FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(22oct1995) & Land == "Berlin"
	replace Est_opp = ( FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(10oct1999) & Land == "Berlin"
	replace Est_opp = ( FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(21oct2001) & Land == "Berlin"
	replace Est_opp = ( CDU + FDP + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(17sep2006) & Land == "Berlin"
	replace Est_opp = ( CDU + FDP + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(18sep2011) & Land == "Berlin"
	replace Est_opp = ( FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(18sep2016) & Land == "Berlin"
	
	//LW : Brandenburg
	replace Est_opp = ( CDU + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(11sep1994) & Land == "Brandenburg"
	replace Est_opp = ( CDU + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(05sep1999) & Land == "Brandenburg"
	replace Est_opp = ( FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(19sep2004) & Land == "Brandenburg"
	replace Est_opp = ( FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(27sep2009) & Land == "Brandenburg"
	replace Est_opp = ( CDU + FDP + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(14sep2014) & Land == "Brandenburg"
	replace Est_opp = ( CDU + FDP + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(1sep2019) & Land == "Brandenburg"
	
	//LW : Bremen
	replace Est_opp = ( CDU + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(25sep1983) & Land == "Bremen"
	replace Est_opp = ( CDU + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(13sep1987) & Land == "Bremen"
	replace Est_opp = ( CDU + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(29sep1991) & Land == "Bremen"
	replace Est_opp = ( CDU + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(14may1995) & Land == "Bremen"
	replace Est_opp = ( FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(06jun1999) & Land == "Bremen"
	replace Est_opp = ( FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(25may2003) & Land == "Bremen"
	replace Est_opp = ( FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(13may2007) & Land == "Bremen"
	replace Est_opp = ( CDU + FDP + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(22may2011) & Land == "Bremen"
	replace Est_opp = ( CDU + FDP + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(10may2015) & Land == "Bremen"
	replace Est_opp = ( CDU + FDP + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(26may2019) & Land == "Bremen"

	//LW : Hamburg
	replace Est_opp = ( CDU + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(06jun1982) & Land == "Hamburg"
	replace Est_opp = ( CDU + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(09nov1986) & Land == "Hamburg"
	replace Est_opp = ( CDU + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(02may1991) & Land == "Hamburg"
	replace Est_opp = ( CDU + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(19sep1993) & Land == "Hamburg"
	replace Est_opp = ( CDU + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(21sep1997) & Land == "Hamburg"
	replace Est_opp = ( CDU + FDP + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(23sep2001) & Land == "Hamburg"
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(29feb2004) & Land == "Hamburg"
	replace Est_opp = ( SPD + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(27feb2008) & Land == "Hamburg"
	replace Est_opp = ( SPD + FDP + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(20feb2011) & Land == "Hamburg"
	replace Est_opp = ( CDU + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(15feb2015) & Land == "Hamburg"

	//LW : Hessen
	replace Est_opp = ( CDU + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(26sep1982) & Land == "Hessen"
	replace Est_opp = ( SPD + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(20jan1991) & Land == "Hessen"
	replace Est_opp = ( CDU + FDP + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(07feb1999) & Land == "Hessen"
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(02feb2003) & Land == "Hessen"
	replace Est_opp = ( SPD + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(27jan2008) & Land == "Hessen"
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(22sep2013) & Land == "Hessen"
	replace Est_opp = ( SPD + FDP + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(28oct2018) & Land == "Hessen"

	//LW : Mecklenburg-Vorpommern
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(16oct1994) & Land == "Mecklenburg-Vorpommern"
	replace Est_opp = ( FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(27sep1998) & Land == "Mecklenburg-Vorpommern"
	replace Est_opp = ( CDU + FDP + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(22sep2002) & Land == "Mecklenburg-Vorpommern"
	replace Est_opp = ( CDU + FDP + GRUNE ) if election_typeBWorLW == "LW" & election_date == td(17sep2006) & Land == "Mecklenburg-Vorpommern"
	replace Est_opp = ( FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(04sep2011) & Land == "Mecklenburg-Vorpommern"
	replace Est_opp = ( FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(04sep2016) & Land == "Mecklenburg-Vorpommern"
	
	//LW : Niedersachsen
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(13may1990) & Land == "Niedersachsen"
	replace Est_opp = ( CDU + FDP + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(13mar1994) & Land == "Niedersachsen"
	replace Est_opp = ( CDU + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(01mar1998) & Land == "Niedersachsen"
	replace Est_opp = ( CDU + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(02feb2003) & Land == "Niedersachsen"
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(27jan2008) & Land == "Niedersachsen"
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(20jan2013) & Land == "Niedersachsen"
	replace Est_opp = ( CDU + FDP + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(15oct2017) & Land == "Niedersachsen"

	//LW : Nordrhein-Westfalen
	replace Est_opp = ( CDU + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(11may1980) & Land == "Nordrhein-Westfalen"
	replace Est_opp = ( CDU + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(12may1985) & Land == "Nordrhein-Westfalen"
	replace Est_opp = ( CDU + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(13may1990) & Land == "Nordrhein-Westfalen"
	replace Est_opp = ( CDU + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(14may1995) & Land == "Nordrhein-Westfalen"
	replace Est_opp = ( CDU + FDP + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(14may2000) & Land == "Nordrhein-Westfalen"
	replace Est_opp = ( CDU + FDP + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(22may2005) & Land == "Nordrhein-Westfalen"
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(09may2010) & Land == "Nordrhein-Westfalen"
	replace Est_opp = ( CDU + FDP + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(13may2012) & Land == "Nordrhein-Westfalen"
	replace Est_opp = ( CDU + FDP + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(14may2017) & Land == "Nordrhein-Westfalen"
	
	//LW : Rheinland-Pfalz
	replace Est_opp = ( SPD + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(06mar1983) & Land == "Rheinland-Pfalz"
	replace Est_opp = ( SPD + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(17may1987) & Land == "Rheinland-Pfalz"
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(21apr1991) & Land == "Rheinland-Pfalz"
	replace Est_opp = ( CDU + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(24mar1996) & Land == "Rheinland-Pfalz"
	replace Est_opp = ( CDU + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(25mar2001) & Land == "Rheinland-Pfalz"
	replace Est_opp = ( CDU + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(26mar2006) & Land == "Rheinland-Pfalz"
	replace Est_opp = ( CDU + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(27mar2011) & Land == "Rheinland-Pfalz"
	replace Est_opp = ( CDU + FDP + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(13mar2016) & Land == "Rheinland-Pfalz"
	
	//LW : Saarland
	replace Est_opp = ( FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(27apr1980) & Land == "Saarland"
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(10mar1985) & Land == "Saarland"
	replace Est_opp = ( CDU + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(28jan1990) & Land == "Saarland"
	replace Est_opp = ( CDU + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(16oct1994) & Land == "Saarland"
	replace Est_opp = ( CDU + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(05sep1999) & Land == "Saarland"
	replace Est_opp = ( SPD + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(05sep2004) & Land == "Saarland"
	replace Est_opp = ( SPD + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(30aug2009) & Land == "Saarland"
	replace Est_opp = ( SPD + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(25mar2012) & Land == "Saarland"
	replace Est_opp = ( FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(26mar2017) & Land == "Saarland"
	
	//LW : Sachsen
	replace Est_opp = ( SPD + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(11sep1994) & Land == "Sachsen"
	replace Est_opp = ( SPD + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(19sep1999) & Land == "Sachsen"
	replace Est_opp = ( SPD + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(19sep2004) & Land == "Sachsen"
	replace Est_opp = ( FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(30aug2009) & Land == "Sachsen"
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(31aug2014) & Land == "Sachsen"
	
	//LW : Sachsen-Anhalt
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(26jun1994) & Land == "Sachsen-Anhalt"
	replace Est_opp = ( FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(26apr1998) & Land == "Sachsen-Anhalt"
	replace Est_opp = ( CDU + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(21apr2002) & Land == "Sachsen-Anhalt"
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(26mar2006) & Land == "Sachsen-Anhalt"
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(20mar2011) & Land == "Sachsen-Anhalt"
	replace Est_opp = ( FDP + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(13mar2016) & Land == "Sachsen-Anhalt"
	
	//LW : Schleswig-Holstein
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(08may1988) & Land == "Schleswig-Holstein"
	replace Est_opp = ( CDU + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(05apr1992) & Land == "Schleswig-Holstein"
	replace Est_opp = ( CDU + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(24mar1996) & Land == "Schleswig-Holstein"
	replace Est_opp = ( CDU + FDP + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(27feb2000) & Land == "Schleswig-Holstein"
	replace Est_opp = ( CDU + FDP + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(20feb2005) & Land == "Schleswig-Holstein"
	replace Est_opp = ( FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(27sep2009) & Land == "Schleswig-Holstein"
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(06may2012) & Land == "Schleswig-Holstein"
	replace Est_opp = ( CDU + FDP + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(07may2017) & Land == "Schleswig-Holstein"
	
	//LW : Thüringen
	replace Est_opp = ( SPD + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(16oct1994) & Land == "Thüringen"
	replace Est_opp = ( FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(12sep1999) & Land == "Thüringen"
	replace Est_opp = ( SPD + FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(13jun2004) & Land == "Thüringen"
	replace Est_opp = ( FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(30aug2009) & Land == "Thüringen"
	replace Est_opp = ( FDP + GRUNE + DIELINKE ) if election_typeBWorLW == "LW" & election_date == td(14sep2014) & Land == "Thüringen"
	replace Est_opp = ( CDU + FDP ) if election_typeBWorLW == "LW" & election_date == td(27oct2019) & Land == "Thüringen"
	
*Votes shares
foreach x of varlist CDU-SonstigeParteien OtherParties-Est_opp {
	gen `x'_share=(`x'/Valid)
}  // x

*Drop redundant variables
drop CDU-Est_opp

*Generate year
gen year = year(election_date)

replace Land = subinstr(Land, "-", "_", .)

*Save dataset
sort Land kreis_code election_date election_typeBWorLW

qui compress
save "${tempdata}kreise_elections.dta", replace

erase "${tempdata}BW_Kreis.dta"
erase "${tempdata}LW_Kreis.dta"


**Create datasets for each State to use for the merge
local lander "Baden_Württemberg Bayern Berlin Brandenburg Bremen Hamburg Hessen Mecklenburg_Vorpommern Niedersachsen Nordrhein_Westfalen Rheinland_Pfalz Saarland Sachsen Sachsen_Anhalt Schleswig_Holstein Thüringen"
foreach l of local lander {
	preserve
		keep if Land == "`l'"
		save "${tempdata}/`l'_elections.dta", replace
	restore
} // l



*------------------------------------------------------------------------------*

*** POLLUTION DATASET

use "${rawdata}pollution/pollution_UBA_d_19902019.dta", clear

keep station year month day PM10_ug PM2_5_mg O3_ug NO2_ug

**Recode negative values as 0
foreach x of varlist PM10_ug PM2_5_mg O3_ug NO2_ug {
	replace `x' = 0 if `x' < 0 & `x' != 0		// PM10: 177; PM2.5: 112; O3: 116; NO2: 497
}

**Create lags and leads
sort station year month day

foreach x of varlist PM10_ug O3_ug {
	forval t = 1/30 {
	//Lags
	bysort station: gen lag_`x'_`t' = `x'[_n-`t']
	//Leads
	bysort station: gen lead_`x'_`t' = `x'[_n+`t']
	} // t
} // x

foreach x of varlist PM10_ug {
	forval t = 1/5 {
	//Sunday Lags
	bysort station: gen lag_`x'_s`t' = `x'[_n-`t'*7]
	//Sunday Leads
	bysort station: gen lead_`x'_s`t' = `x'[_n+`t'*7]
	} // t
} // x


**Save dataset
qui compress
save "${tempdata}pollution_UBA_d_19902019_w_av.dta", replace



*------------------------------------------------------------------------------*

*** WEATHER DATASET

use "${rawdata}pollution/weather_d_19802019.dta"

gen year = year(date)
gen month = month(date)
gen day = day(date)

keep station_DWD year month day temp humid_rel wind_speed precip_amount

rename precip_amount precip

**Create lags and leads
sort station_DWD year month day

foreach x of varlist temp humid_rel wind_speed precip {
	forval t = 1/30 {
	//Lags
	bysort station_DWD: gen lag_`x'_`t' = `x'[_n-`t']
	//Leads
	bysort station_DWD: gen lead_`x'_`t' = `x'[_n+`t']
	} // t
} // x


*Save dataset
qui compress
save "${tempdata}weather_d_19802019_w_av.dta", replace



*------------------------------------------------------------------------------*

*** DEMOGRAPHIC AND ECONOMIC VARIABLES

**Population
forvalues years = 2000/2019 {

	*Use data 
	import excel "${rawdata}demoecon/population_`years'.xlsx", cellrange(A5) clear

	*Gen year
	gen year =. 
	replace year = `years'

	*Varnames 
	rename A kreis 
	rename B kreisname
	rename C altersgruppe 
	rename D pop_tot
	rename E pop_m
	rename F pop_f

	*Replace missings
	replace pop_tot = "" if pop_tot == "-"
	replace pop_m = "" if pop_m == "-"
	replace pop_f = "" if pop_f == "-"

	*Kreise
	replace kreis = trim(kreis)
	replace kreisname = trim(kreisname)

	carryforward kreisname kreis, replace 

	*Reshape preparation
	drop if altersgruppe == ""

	gen alter = ""

	replace alter = "0_3" if altersgruppe == "unter 3 Jahre"
	replace alter = "3_6" if altersgruppe == "3 bis unter 6 Jahre"
	replace alter = "6_10" if altersgruppe == "6 bis unter 10 Jahre"
	replace alter = "10_15" if altersgruppe == "10 bis unter 15 Jahre"
	replace alter = "15_18" if altersgruppe == "15 bis unter 18 Jahre"
	replace alter = "18_20" if altersgruppe == "18 bis unter 20 Jahre"
	replace alter = "20_25" if altersgruppe == "20 bis unter 25 Jahre"
	replace alter = "25_30" if altersgruppe == "25 bis unter 30 Jahre"
	replace alter = "30_35" if altersgruppe == "30 bis unter 35 Jahre"
	replace alter = "35_40" if altersgruppe == "35 bis unter 40 Jahre"
	replace alter = "40_45" if altersgruppe == "40 bis unter 45 Jahre"
	replace alter = "45_50" if altersgruppe == "45 bis unter 50 Jahre"
	replace alter = "50_55" if altersgruppe == "50 bis unter 55 Jahre"
	replace alter = "55_60" if altersgruppe == "55 bis unter 60 Jahre"
	replace alter = "60_65" if altersgruppe == "60 bis unter 65 Jahre"
	replace alter = "65_75" if altersgruppe == "65 bis unter 75 Jahre"
	replace alter = "75plus" if altersgruppe == "75 Jahre und mehr"
	replace alter = "alle" if altersgruppe == "Insgesamt"

	drop altersgruppe

	*Reshape wide
	reshape wide pop_tot pop_m pop_f , i(kreis year) j(alter) string

	*Label var 
	label var pop_tot0_3	"Total population under 3 years"
	label var pop_tot3_6	"Total population from 3 to under 6 years"
	label var pop_tot6_10	"Total population from 6 to under 10 years"
	label var pop_tot10_15	"Total population from 10 to under 15 years"
	label var pop_tot15_18	"Total population from 15 to under 18 years"
	label var pop_tot18_20	"Total population from 18 to under 20 years"
	label var pop_tot20_25	"Total population from 20 to under 25 years"
	label var pop_tot25_30	"Total population from 25 to under 30 years"
	label var pop_tot30_35	"Total population from 30 to under 35 years"
	label var pop_tot35_40	"Total population from 35 to under 40 years"
	label var pop_tot40_45	"Total population from 40 to under 45 years"
	label var pop_tot45_50	"Total population from 45 to under 50 years"
	label var pop_tot50_55	"Total population from 50 to under 55 years"
	label var pop_tot55_60	"Total population from 55 to under 60 years"
	label var pop_tot60_65	"Total population from 60 to under 65 years"
	label var pop_tot65_75	"Total population from 65 to under 75 years"
	label var pop_tot75plus	"Total population 75 years and more"
	label var pop_totalle	"Total population"

	label var pop_m0_3		"Total male population under 3 years"
	label var pop_m3_6		"Total male population from 3 to under 6 years"
	label var pop_m6_10		"Total male population from 6 to under 10 years"
	label var pop_m10_15	"Total male population from 10 to under 15 years"
	label var pop_m15_18	"Total male population from 15 to under 18 years"
	label var pop_m18_20	"Total male population from 18 to under 20 years"
	label var pop_m20_25	"Total male population from 20 to under 25 years"
	label var pop_m25_30	"Total male population from 25 to under 30 years"
	label var pop_m30_35	"Total male population from 30 to under 35 years"
	label var pop_m35_40	"Total male population from 35 to under 40 years"
	label var pop_m40_45	"Total male population from 40 to under 45 years"
	label var pop_m45_50	"Total male population from 45 to under 50 years"
	label var pop_m50_55	"Total male population from 50 to under 55 years"
	label var pop_m55_60	"Total male population from 55 to under 60 years"
	label var pop_m60_65	"Total male population from 60 to under 65 years"
	label var pop_m65_75	"Total male population from 65 to under 75 years"
	label var pop_m75plus	"Total male population 75 years or more"
	label var pop_malle		"Total male population"

	label var pop_f0_3		"Total female population under 3 years"
	label var pop_f3_6		"Total female population from 3 to under 6 years"
	label var pop_f6_10		"Total female population from 6 to under 10 years"
	label var pop_f10_15	"Total female population from 10 to under 15 years"
	label var pop_f15_18	"Total female population from 15 to under 18 years"
	label var pop_f18_20	"Total female population from 18 to under 20 years"
	label var pop_f20_25	"Total female population from 20 to under 25 years"
	label var pop_f25_30	"Total female population from 25 to under 30 years"
	label var pop_f30_35	"Total female population from 30 to under 35 years"
	label var pop_f35_40	"Total female population from 35 to under 40 years"
	label var pop_f40_45	"Total female population from 40 to under 45 years"
	label var pop_f45_50	"Total female population from 45 to under 50 years"
	label var pop_f50_55	"Total female population from 50 to under 55 years"
	label var pop_f55_60	"Total female population from 55 to under 60 years"
	label var pop_f60_65	"Total female population from 60 to under 65 years"
	label var pop_f65_75	"Total female population from 65 to under 75 years"
	label var pop_f75plus	"Total female population 75 years or more"
	label var pop_falle		"Total female population"

	*Finishing up the data 
	destring pop_tot0_3 - pop_falle, replace
	
	*Construct reformed counties
	//Gottingen Landkreis after addition of Osterode am Harz
		if `years' <= 2015 {
		foreach x of varlist pop_tot0_3-pop_falle {
			sum `x' if kreis == "03152" // Gottingen, Landkreis
			scalar `x'_03152 = r(mean)
			sum `x' if kreis == "03156"  // Osterode am Harz, Landkreis
			scalar `x'_03156 = r(mean)
			replace `x' = `x'_03152 + `x'_03152 if kreis == "03159"
		} //x
	} // years 2000-2015
	
	//Städteregion Aachen (einschl. Stadt Aachen)
	if `years' <= 2008 {
		foreach x of varlist pop_tot0_3-pop_falle {
			sum `x' if kreis == "05334002" // Aachen, kreisfr. stadt
			scalar `x'_05334002 = r(mean)
			sum `x' if kreis == "05354"  // Aachen, Kreis
			scalar `x'_05354 = r(mean)
			replace `x' = `x'_05334002 + `x'_05354 if kreis == "05334"
		} //x
	} // years 2000-2008
	
	gen length = length(kreis)

	drop if length != 5 & kreis != "02" & kreis != "11" & kreis != "05334002" //& kreis != "04"

	replace kreis = "02000" if kreis=="02"
	replace kreis = "11000" if kreis=="11"

	drop length

	drop if substr(kreis,1,2) == "14" & pop_tot0_3 == .  // Kreise Saxonia double, half filled with missings 
	drop if substr(kreis,1,2) == "15" & pop_tot0_3 == .  // Kreise Saxonia-Anhalt double, half filled with missings
	
	*Save
	order kreis kreisname year pop_tot0_3 - pop_falle

	qui compress
	save "${tempdata}population_kreis_`years'.dta", replace 

} //years

*Combine
clear
foreach years in 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 { //
	append using 	"${tempdata}population_kreis_`years'.dta"
	erase 			"${tempdata}population_kreis_`years'.dta"
}

*Population 18-64
egen pop_tot18_64 = rowtotal(pop_tot18_20 pop_tot20_25 pop_tot25_30 pop_tot30_35 pop_tot35_40 pop_tot40_45 pop_tot45_50 pop_tot50_55 pop_tot55_60 pop_tot60_65), miss

label var pop_tot18_64 	"Total poplation 18-64 years"

*Population 15-64
egen pop_tot15_64 	= rowtotal(pop_tot15_18 pop_tot18_20 pop_tot20_25 pop_tot25_30 pop_tot30_35 pop_tot35_40 pop_tot40_45 pop_tot45_50 pop_tot50_55 pop_tot55_60 pop_tot60_65), miss
egen pop_age1564_m 		= rowtotal(pop_m15_18 pop_m18_20 pop_m20_25 pop_m25_30 pop_m30_35 pop_m35_40 pop_m40_45 pop_m45_50 pop_m50_55 pop_m55_60 pop_m60_65), miss
egen pop_age1564_f 		= rowtotal(pop_f15_18 pop_f18_20 pop_f20_25 pop_f25_30 pop_f30_35 pop_f35_40 pop_f40_45 pop_f45_50 pop_f50_55 pop_f55_60 pop_f60_65), miss

lab var pop_tot15_64 	"Total population 15-64 years"
lab var pop_age1564_m 	"Total male population 15-64 years" 
lab var pop_age1564_f 	"Total female population 15-64 years"

*Save
qui compress
sort kreis year
save "${tempdata}population_kreis.dta", replace 

clear

///////////////////////////////////////////////////

**GDP
forvalue years = 2000/2018 { 

	*Use data
	import excel "${rawdata}demoecon/GDP_`years'.xlsx", cellrange(A5) clear

	*Gen year 
	gen year =. 
	replace year = `years'

	*Recode strings
	replace C ="" if C == "-"
	replace D ="" if D == "-"
	replace E ="" if E == "-"
	replace F ="" if F == "-"
	replace G ="" if G == "-"
	replace H ="" if H == "-"
	replace I ="" if I == "-"
	replace J ="" if J == "-"
	replace K ="" if K == "-"
	replace L ="" if L == "-"
	replace M ="" if M == "-"

	replace C ="" if C == "."
	replace D ="" if D == "."
	replace E ="" if E == "."
	replace F ="" if F == "."
	replace G ="" if G == "."
	replace H ="" if H == "."
	replace I ="" if I == "."
	replace J ="" if J == "."
	replace K ="" if K == "."
	replace L ="" if L == "."
	replace M ="" if M == "."

	*Varnames 
	rename A kreis 
	rename B kreisname
	rename C bip 
	rename D bip_ew
	rename E bip_einw
	rename F bws_insgesamt
	rename G bws_landforst
	rename H bws_prod
	rename I bws_verarb
	rename J bws_bau
	rename K bws_handel
	rename L bws_dienstl
	rename M bws_oefftldienst
	
	*Label var
	label var kreis 		  "Kreis ID"
	label var kreisname 	  "Kreis Name"
	label var bip 			  "GDP" 
	label var bip_ew		  "GDP per worker"
	label var bip_einw		  "GDP per capita"
	label var bws_insgesamt	  "Total GVA at basic prices (in current prices)"
	label var bws_landforst	  "GVA agriculture and forestry, fishing"
	label var bws_prod		  "GVA manufacturing industry (excluding construction)" 
	label var bws_verarb	  "GVA transormation" 
	label var bws_bau 	      "GVA construction industry"
	label var bws_handel 	  "GVA trade, transport, hospitality, information, communication"
	label var bws_dienstl	  "GVA finance, insurance, business services, land, housing"
	label var bws_oefftldienst"GVA public and other services, education, health" 

	*Finishing up the data 
	drop if kreis=="05354" //Aachen all missings

	gen length = length(kreis)

	drop if length != 5 & kreis != "02" & kreis != "11" //excludes all county data as well as Hannover, Landeshauptstadt and Saarbrücken, Landeshauptstadt

	replace kreis = "02000" if kreis=="02"
	replace kreis = "11000" if kreis=="11"
		
	drop length
	
	replace bws_landforst = "" if bws_landforst == "/"

	destring bip-bws_oefftldienst, replace
	
	*Save
	order kreis kreisname year bip-bws_oefftldienst

	qui compress
	save "${tempdata}GDP_kreis_`years'.dta", replace 

} //years

*Combine
clear
forvalue years = 2000/2018 { 
	append using 	"${tempdata}GDP_kreis_`years'.dta"
	erase 			"${tempdata}GDP_kreis_`years'.dta"
}

*Save
qui compress
sort kreis year
save "${tempdata}GDP_kreis.dta", replace 

clear

//////////////////////////////////////////////////////

**Employment
forvalue years = 2000/2018 {

	*Use data
	import excel "${rawdata}demoecon/employment_`years'.xlsx", cellrange(A6) clear

	*Gen year
	gen year = .
	replace year = `years'
	replace year = year[_n-1] if year==.

	*Kreise
	keep if strpos(A,"0")==1 | strpos(A,"1")==1
	replace A = "02000" if A=="02"
	replace A = "11000" if A=="11"
	keep if length(A)==5

	*Varnames
	rename A kreis
	rename B kreisname
	rename C erw_total
	rename D erw_WZ2008_A
	rename E erw_WZ2008_BE
	rename F erw_WZ2008_C
	rename G erw_WZ2008_F
	rename H erw_WZ2008_Handel
	rename I erw_WZ2008_Finanz
	rename J erw_WZ2008_Erziehung
	
	*Label var
	lab var kreis 					"Kreis ID"
	lab var kreisname 				"Kreis Name"
	lab var year 					"Year (annual average)"
	lab var erw_total 				"Total employed (in 1000)"
	lab var erw_WZ2008_A			"Employed in agriculture, forestry, fishing (A) (in 1000)"
	lab var erw_WZ2008_BE			"Employed in manufacturing industry excluding construction (B-E) (in 1000)"
	lab var erw_WZ2008_C			"Employed in transformation (C) (in 1000)"
	lab var erw_WZ2008_F			"Employed in construction (F) (in 1000)"
	lab var erw_WZ2008_Handel		"Employed in trade, transport, hospitality, information, communication (in 1000)"
	lab var erw_WZ2008_Finanz		"Employed in finance, insurance, business services, land, housing (in 1000)"
	lab var erw_WZ2008_Erziehung 	"Employed in public and other services, education, health (in 1000)"
	
	*Finishing up the data
	drop if kreis=="05354" //Aachen all missings
	destring erw_*, replace force
	
	egen erw_miss = rowtotal(erw_*), miss
	drop if erw_miss==.
	drop erw_miss
	
	*Save
	order kreis kreisname year erw_*
	
	replace kreis = trim(kreis)
	
	qui compress
	save "${tempdata}employment_kreis_`years'.dta", replace 
	
} //years

*Year 2019
import excel "${rawdata}demoecon/ETR_R2B1_2019_hj.xlsx", sheet("Tab 1") cellrange(B9:AK541) clear
rename B kreis
rename H kreisname
drop if D == "TH" | D == "HB"  // Thuringen and Bremen will be added separately
drop C-G
drop if kreis == ""
replace kreis = "02000" if kreis=="02"
replace kreis = "11000" if kreis=="11"
drop if length(kreis)<5
rename AK erw_total
keep kreis kreisname erw_tot
destring erw_tot, replace
gen year = 2019
order kreis kreisname year erw_*
save "${tempdata}employment_kreis_2019.dta", replace

*Thuringen county-level employment
clear
forvalue year = 2000/2019 {
	import excel "${rawdata}demoecon/Thuringen_employment_`year'.xlsx", cellrange(A22:C44) allstring clear
	gen year = .
	replace year = `year'
	replace year = year[_n-1] if year==.
	rename A kreis
	rename B kreisname
	rename C erw_total
	replace kreis = "160" + kreis
	replace erw_total = subinstr(erw_total, ",", ".", .)
	destring erw_total, replace
	qui compress
	save "${tempdata}Thuringen_employment_`year'.dta", replace
}
clear
forvalue years = 2000/2019 { 
	append using 	"${tempdata}Thuringen_employment_`years'.dta"
	erase 			"${tempdata}Thuringen_employment_`years'.dta"
}
qui compress
sort kreis year
save "${tempdata}Thuringen_employment_kreis.dta", replace

*Bremen county-level employment
import excel "${rawdata}demoecon/ETR_R2B1_2019_hj.xlsx", sheet("Tab 1") cellrange(B9:AK541) clear
keep if D == "HB"  // kepp Bremen
rename B kreis
rename H kreisname
drop C-G
drop if kreis == ""
drop I-Q
drop if length(kreis)<5
rename R erw_total2000
rename S erw_total2001
rename T erw_total2002
rename U erw_total2003
rename V erw_total2004
rename W erw_total2005
rename X erw_total2006
rename Y erw_total2007
rename Z erw_total2008
rename AA erw_total2009
rename AB erw_total2010
rename AC erw_total2011
rename AD erw_total2012
rename AE erw_total2013
rename AF erw_total2014
rename AG erw_total2015
rename AH erw_total2016
rename AI erw_total2017
rename AJ erw_total2018
rename AK erw_total2019
reshape long erw_total, i(kreis) j(year)
destring erw_total, replace
qui compress
sort kreis year
save "${tempdata}Bremen_employment_kreis.dta", replace
	
*Combine	
clear
//Years 2000-2019
forvalue years = 2000/2019 { 
	append using 	"${tempdata}employment_kreis_`years'.dta"
	erase 			"${tempdata}employment_kreis_`years'.dta"
}
//Thuringen
append using "${tempdata}Thuringen_employment_kreis.dta"
erase "${tempdata}Thuringen_employment_kreis.dta"
//Bremen
merge 1:1 kreis year using "${tempdata}Bremen_employment_kreis.dta", nogen update
erase "${tempdata}Bremen_employment_kreis.dta"

*Save
qui compress
sort kreis year
save "${tempdata}employment_kreis.dta", replace 

clear

////////////////////////////////////////////////////////

**Full demographic and economic dataset

use "${tempdata}population_kreis.dta", clear

*Merge GDP
merge 1:1 kreis year using "${tempdata}GDP_kreis.dta", nogen
*Merge employment
merge 1:1 kreis year using "${tempdata}employment_kreis.dta", nogen

*Save
save "${tempdata}demo_econ_var.dta", replace

*Generate kreis_code
rename kreis kreis_code
destring kreis_code, replace

*Drop variales
drop pop_tot0_3-pop_f75plus

*Rename variables
rename kreisname kreis_name
rename pop_totalle pop_tot
rename pop_malle pop_m
rename pop_falle pop_f
rename bip GDP
rename bip_ew GDP_worker
rename bip_einw GDP_capita
rename erw_total employed_tot

*Add population for Mecklenburg-Vorpommern pre-reform
merge 1:1 kreis_code year using "${tempdata}MV_reformed_counties_pop.dta", keepusing(pop_tot pop_tot15_64) update nogen

*Generate employment rate
gen employment = (employed_tot * 1000) / pop_tot15_64
label var employment "Employment rate"

*Save
qui compress
save "${tempdata}demo_econ_var.dta", replace

**Create files for Hannover and Saarbrucken to be merged saparately for missing observations
*Hannover 
preserve
keep if kreis_name == "Region Hannover, Landkreis"
replace kreis_code = 3241001
save "${tempdata}Hannover.dta", replace
restore

*Saarbrucken
preserve
keep if kreis_name == "Saarbrücken, Regionalverband"
replace kreis_code = 10041100
save "${tempdata}Saarbrucken.dta", replace
restore

*------------------------------------------------------------------------------*

clear

exit
